package com.orange.modules.dao;

import java.sql.SQLException;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.orange.dbhelp.DbHelper;
import com.orange.dbhelp.page.Page;
import com.orange.modules.entity.AuthRectificationReport;
import com.orange.modules.entity.expert.AuthExpert;

@Repository
public class AuthRectificationReportRepository {

	@Resource(name = "dbHelper")
	private DbHelper dbHelper;
	
	@Autowired
	private AuthRectificationReportDao authRectificationReportDao;
	
	/**
	 * 查询单条数据根据学校id
	 * @throws SQLException 
	 */
	public AuthRectificationReport findRectificationReportBySchoolId(String sid) throws SQLException{
		String sql = "select a.p_id as pid, a.s_school_id as schoolId, a.s_filepath as filePath, a.s_filename as fileName," +
				" a.s_create_time as createTime, a.s_year as year from t_auth_rectification_report as a where a.s_school_id = ?";
		return dbHelper.getBean(sql, AuthRectificationReport.class, sid);
	}
	
	/**
	 * 保存
	 */
	public void saveRectificationReport(AuthRectificationReport rr){
		authRectificationReportDao.save(rr);
	}
	
	/**
	 * 删除
	 */
	public void deleteRectificationReport(String pid){
		authRectificationReportDao.delete(pid);
	}
	
	/**
	 * 查询单条数据
	 */
	public AuthRectificationReport findOne(String pid){
		AuthRectificationReport rr = authRectificationReportDao.findOne(pid);
		return rr;
	}
	
	/**
	 * 分页查询
	 * @throws SQLException 
	 */
	public Page<AuthRectificationReport> queryAllReport(Map<String ,String> map) throws SQLException{
		StringBuilder sql = new StringBuilder("select TOP 1000 a.p_id as pid, a.s_school_id as schoolId, a.s_filepath as filePath," +
				" a.s_filename as fileName, a.s_create_time as createTime, a.s_year as year, a.s_state as state, b.s_school_name as schoolName, b.s_major as major" +
				" from t_auth_group as c left join t_auth_school as b on c.s_school_id = b.p_id left join t_auth_rectification_report as a on a.s_school_id = c.s_school_id" +
				" where 1=1 and c.s_state = '3' and c.s_type = '1'");
		if(StringUtils.isNotBlank(map.get("major"))){
			sql.append(" and b.s_major = '"+map.get("major")+"'");
		}
		if(StringUtils.isNotBlank(map.get("schoolName"))){
			sql.append(" and b.s_school_name like '%"+map.get("schoolName")+"%'");
		}
		sql.append(" ORDER BY a.s_create_time DESC ");
		Page<AuthRectificationReport> page = dbHelper.getPage(sql.toString(), AuthRectificationReport.class,Integer.valueOf(map.get("pageNumber")),
				Integer.valueOf(map.get("pageSize")), new Object[] {});
		return page;
	}
}
